﻿using System.Data;
using System;
using MySql.Data.MySqlClient;

namespace db
{
    
    public static class dbFunction
    {
        public struct FieldAndValue
        {
            
            public string Field;
            public object Value;
        }
        public static DataTable Query(MySqlConnection conn, string sql)
        {

            if (conn.State != ConnectionState.Open) conn.Open();
            using (DataTable dt = new DataTable())
            {
                MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                da.Fill(dt);

                return dt;
            }
        }
        public static DataTable Query(MySqlConnection conn, string table, object[,] F_V)
        {
            string sql = "";
            sql = "select * from `" + table + "` where ";
            for (int i = 0; i < F_V.Length / F_V.Rank; i++)
            {
                sql = sql + "`" + F_V[i, 0] + "` = " + "'" + F_V[i, 1].ToString () + "' AND ";
            }
            sql = sql.Substring(0, sql.Length - 4);
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                using (DataTable dt = new DataTable())
                {
                    MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                    da.Fill(dt);

                    return dt;
                }
            }
            catch (Exception e)
            {
                General.LogFunc.LogWriteInDay(e.ToString());
                return new DataTable ();
            }
        
        }

        public static void Update(MySqlConnection conn, string sql)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            MySqlCommand cmd = new MySqlCommand(sql);
            cmd.ExecuteNonQuery();

        }

        public static void Insert(MySqlConnection conn, string table, string fields, string values)
        {
            MySql.Data.MySqlClient.MySqlCommand mc = new MySql.Data.MySqlClient.MySqlCommand(string.Format(@"INSERT INTO `{0}` ({1}) VALUES ({2});", table, fields, values), conn);
            mc.ExecuteNonQuery();
        }

        /// <summary>在数据库某个表插入一条记录
        /// </summary>
        /// <param name="conn">MySqlConnection</param>
        /// <param name="table">表名</param>
        /// <param name="F_V">object[n][2]</param>
        /// <returns>如果成功，返回0，如果每条记录不是2元素的object[]，返回-1</returns>

        public static int Insert(MySqlConnection conn, string table, object[,] F_V)
        {
            /*--------------------Example----------------------
            iRet = dbFunction.Insert(conn, "correlation", new object[,]
                {
                    {"testsn",22},
                    {"line",123},
                    {"measure",111},
                    {"testplan","333"}
                });
            -------------------------------------------------*/
            string fields = ",";
            string values = ",";
            int i;
            for (i = 0; i < F_V.Length / F_V.Rank; i++)
            {
                fields = fields + ",`" + F_V[i, 0] + "`";
                values = values + ",'" + F_V[i, 1] + "'";
            }

            fields = fields.Substring(2);
            values = values.Substring(2);
            MySql.Data.MySqlClient.MySqlCommand mc = new MySql.Data.MySqlClient.MySqlCommand(string.Format(@"INSERT INTO `{0}` ({1}) VALUES ({2});", table, fields, values), conn);
            try
            {
                mc.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                General.LogFunc.LogWrite(ex.ToString() + "\r\n-------------------------MySQL Command-----------------\r\n" + mc.CommandText, "MysqlErr.log");
            }
            return 0;
        }

        public static MySqlConnection ConnectTo(string Server, string Uid, string Pass, string database)
        {
            try
            {
                return new MySqlConnection(string.Format("server={0};uid={1};pwd={2};database={3}", Server, Uid, Pass, database));
            }
            catch (Exception ex)
            {
                General.LogFunc.LogWrite(ex.ToString(), "MysqlErr.log");
                return null;
            }
        }

        public static DataTable ConvertDataReaderToDataTable(MySql.Data.MySqlClient.MySqlDataReader reader)
        {
            try
            {
                DataTable objDataTable = new DataTable();
                int intFieldCount = reader.FieldCount;
                for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
                {
                    objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
                }

                objDataTable.BeginLoadData();

                object[] objValues = new object[intFieldCount];
                while (reader.Read())
                {
                    reader.GetValues(objValues);
                    objDataTable.LoadDataRow(objValues, true);
                }
                reader.Close();
                objDataTable.EndLoadData();

                return objDataTable;

            }
            catch (Exception ex)
            {
                throw new Exception("转换出错!", ex);
            }

        }
    }
}